const { pool } = require('../config/database');
const bcrypt = require('bcryptjs');

class User {
  // 获取所有用户
  static async findAll(page = 1, limit = 10, options = {}) {
    try {
      const { search = '', status, roleId, deptId } = options;
      const offset = (page - 1) * limit;
      let query = `
        SELECT u.*, r.name as role_name, d.name as department_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
      `;
      
      // 动态拼接where条件
      const where = [];
      const params = [];
      if (search) {
        where.push('(u.username LIKE ? OR u.email LIKE ? OR u.phone LIKE ?)');
        params.push(`%${search}%`, `%${search}%`, `%${search}%`);
      }
      if (status) {
        where.push('u.status = ?');
        params.push(status);
      }
      if (roleId) {
        where.push('u.role_id = ?');
        params.push(roleId);
      }
      if (deptId) {
        where.push('u.department_id = ?');
        params.push(deptId);
      }
      if (where.length > 0) {
        query += ' WHERE ' + where.join(' AND ');
      }
      query += ' ORDER BY u.create_time DESC LIMIT ? OFFSET ?';
      params.push(Number(limit), Number(offset));
      
      const [rows] = await pool.execute(query, params);
      
      // 获取总数
      let countQuery = 'SELECT COUNT(*) as total FROM users u';
      const countParams = [];
      if (where.length > 0) {
        countQuery += ' WHERE ' + where.join(' AND ');
        countParams.push(...params.slice(0, params.length - 2)); // 不要limit/offset
      }
      const [countResult] = await pool.execute(countQuery, countParams);
      
      return {
        users: rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total: countResult[0].total,
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(`获取用户列表失败: ${error.message}`);
    }
  }

  // 根据ID获取用户
  static async findById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT u.*, r.name as role_name, d.name as department_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        WHERE u.id = ?
      `, [id]);
      
      return rows[0] || null;
    } catch (error) {
      throw new Error(`获取用户失败: ${error.message}`);
    }
  }

  // 根据用户名获取用户
  static async findByUsername(username) {
    try {
      const [rows] = await pool.execute('SELECT * FROM users WHERE username = ?', [username]);
      return rows[0] || null;
    } catch (error) {
      throw new Error(`根据用户名获取用户失败: ${error.message}`);
    }
  }

  // 创建用户（支持多角色，role_id可选）
  static async create(userData) {
    try {
      const { username, password, email, phone, status, role_id, department_id } = userData;
      
      // 检查用户名是否已存在
      const existingUser = await this.findByUsername(username);
      if (existingUser) {
        throw new Error('用户名已存在');
      }
      
      // 加密密码
      const hashedPassword = await bcrypt.hash(password, 10);
      
      const [result] = await pool.execute(`
        INSERT INTO users (username, password, email, phone, status, role_id, department_id)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `, [username, hashedPassword, email, phone, status, role_id || null, department_id]);
      
      return { id: result.insertId, ...userData };
    } catch (error) {
      throw new Error(`创建用户失败: ${error.message}`);
    }
  }

  // 更新用户
  static async update(id, userData) {
    try {
      const { username, email, phone, status, role_id, department_id } = userData;
      
      // 检查用户是否存在
      const existingUser = await this.findById(id);
      if (!existingUser) {
        throw new Error('用户不存在');
      }
      
      // 如果更新用户名，检查是否与其他用户冲突
      if (username && username !== existingUser.username) {
        const userWithSameUsername = await this.findByUsername(username);
        if (userWithSameUsername) {
          throw new Error('用户名已存在');
        }
      }
      
      const [result] = await pool.execute(`
        UPDATE users 
        SET username = ?, email = ?, phone = ?, status = ?, role_id = ?, department_id = ?
        WHERE id = ?
      `, [username, email, phone, status, role_id, department_id, id]);
      
      if (result.affectedRows === 0) {
        throw new Error('更新失败，用户可能不存在');
      }
      
      return await this.findById(id);
    } catch (error) {
      throw new Error(`更新用户失败: ${error.message}`);
    }
  }

  // 更新密码
  static async updatePassword(id, newPassword) {
    try {
      const hashedPassword = await bcrypt.hash(newPassword, 10);
      
      const [result] = await pool.execute(`
        UPDATE users SET password = ? WHERE id = ?
      `, [hashedPassword, id]);
      
      if (result.affectedRows === 0) {
        throw new Error('更新密码失败，用户可能不存在');
      }
      
      return true;
    } catch (error) {
      throw new Error(`更新密码失败: ${error.message}`);
    }
  }

  // 删除用户
  static async delete(id) {
    try {
      const [result] = await pool.execute('DELETE FROM users WHERE id = ?', [id]);
      
      if (result.affectedRows === 0) {
        throw new Error('删除失败，用户可能不存在');
      }
      
      return true;
    } catch (error) {
      throw new Error(`删除用户失败: ${error.message}`);
    }
  }

  // 批量删除用户
  static async deleteMultiple(ids) {
    try {
      const placeholders = ids.map(() => '?').join(',');
      const [result] = await pool.execute(`DELETE FROM users WHERE id IN (${placeholders})`, ids);
      
      return result.affectedRows;
    } catch (error) {
      throw new Error(`批量删除用户失败: ${error.message}`);
    }
  }

  // 验证密码
  static async verifyPassword(password, hashedPassword) {
    return password === hashedPassword;
  }

  // 获取用户统计信息
  static async getStats() {
    try {
      const [totalUsers] = await pool.execute('SELECT COUNT(*) as total FROM users');
      const [activeUsers] = await pool.execute('SELECT COUNT(*) as active FROM users WHERE status = "active"');
      const [inactiveUsers] = await pool.execute('SELECT COUNT(*) as inactive FROM users WHERE status = "inactive"');
      
      return {
        total: totalUsers[0].total,
        active: activeUsers[0].active,
        inactive: inactiveUsers[0].inactive
      };
    } catch (error) {
      throw new Error(`获取用户统计失败: ${error.message}`);
    }
  }

  // 获取用户已分配角色ID
  static async getRoles(userId) {
    try {
      const [rows] = await pool.execute('SELECT role_id FROM user_roles WHERE user_id = ?', [userId]);
      return rows.map(r => r.role_id);
    } catch (error) {
      throw new Error(`获取用户角色失败: ${error.message}`);
    }
  }

  // 分配角色
  static async setRoles(userId, roleIds) {
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      await conn.execute('DELETE FROM user_roles WHERE user_id = ?', [userId]);
      if (roleIds && roleIds.length > 0) {
        const values = roleIds.map(id => [userId, id]);
        await conn.query('INSERT INTO user_roles (user_id, role_id) VALUES ?', [values]);
      }
      await conn.commit();
      return true;
    } catch (error) {
      await conn.rollback();
      throw new Error(`分配角色失败: ${error.message}`);
    } finally {
      conn.release();
    }
  }

  // 获取用户所有菜单（聚合所有角色菜单，自动补全父级节点，返回树结构）
  static async getMenus(userId) {
    const Menu = require('./Menu');
    try {
      // 获取用户所有角色
      const roleIds = await this.getRoles(userId);
      if (roleIds.length === 0) return [];
      // 获取所有分配的菜单ID
      const [menuRows] = await pool.execute(
        `SELECT DISTINCT m.* FROM menus m
         INNER JOIN role_menus rm ON m.id = rm.menu_id
         WHERE rm.role_id IN (${roleIds.map(() => '?').join(',')})
         ORDER BY m.order_num`,
        roleIds
      );
      if (menuRows.length === 0) return [];
      // 查询所有菜单，构建id->menu映射
      const allMenus = await Menu.findAll();
      const menuMap = new Map(allMenus.map(m => [m.id, m]));
      // 收集所有分配菜单及其所有祖先ID
      const idSet = new Set();
      function collectWithParents(menu) {
        if (!menu) return;
        if (!idSet.has(menu.id)) {
          idSet.add(menu.id);
          if (menu.parent_id) {
            collectWithParents(menuMap.get(menu.parent_id));
          }
        }
      }
      menuRows.forEach(menu => collectWithParents(menuMap.get(menu.id)));
      // 取出所有相关菜单，构建树
      const menusForTree = allMenus.filter(m => idSet.has(m.id));
      return Menu.buildMenuTree(menusForTree);
    } catch (error) {
      throw new Error(`获取用户菜单失败: ${error.message}`);
    }
  }
}

module.exports = User;